using System;
using System.Linq;
using System.Threading;
using System.Threading.Tasks;
using System.Data;
using MediatR;
using Microsoft.EntityFrameworkCore;
using RBAC.ErrorCode;
using RBAC.Interstructrue;
using RBAC.Read.Api.Application.Command.ERP.SalesReport;
using RBAC.Read.Api.Application.Dto.ERP.SalesReport;
using Dapper;

namespace RBAC.Read.Api.Application.Handler.ERP.SalesReport
{
    /// <summary>
    /// 开票通知明细 列表查询 处理器（MySQL 兼容）
    /// </summary>
    public class GetBillingNoticeDetailListHandler : IRequestHandler<GetBillingNoticeDetailListCommand, APIResult<GetBillingNoticeDetailListDto>>
    {
        private readonly RBACDbContext _context;
        public GetBillingNoticeDetailListHandler(RBACDbContext context)
        {
            _context = context;
        }

        public async Task<APIResult<GetBillingNoticeDetailListDto>> Handle(GetBillingNoticeDetailListCommand request, CancellationToken cancellationToken)
        {
            try
            {
                var pageIndex = request.PageIndex <= 0 ? 1 : request.PageIndex;
                var pageSize = request.PageSize <= 0 ? 20 : request.PageSize;
                var offset = (pageIndex - 1) * pageSize;

                // 使用 Dapper 联查 BillingAccounting 与 BillingMaterials，并做分组聚合
                // 说明：
                // - 表名/字段名请确保与数据库一致：
                //   BillingAccounting(Id, IsDeleted, BillNumber, BillDate, CustomerName, TaxId, BankName, BankAccount, AddressPhone, ...)
                //   BillingMaterials(Id, IsDeleted, BillingAccountingId, Quantity, Amount, ...)
                // - 如外键或列名不同，请告知以便调整 SQL。

                var conn = _context.Database.GetDbConnection();
                if (conn.State != ConnectionState.Open) await conn.OpenAsync(cancellationToken);

                var sqlWhere = @" WHERE ba.IsDeleted = 0 AND bm.IsDeleted = 0 ";
                var dp = new DynamicParameters();

                if (!string.IsNullOrWhiteSpace(request.BillCode))
                {
                    sqlWhere += " AND ba.BillNumber LIKE CONCAT('%', @BillCode, '%')";
                    dp.Add("BillCode", request.BillCode.Trim());
                }
                if (!string.IsNullOrWhiteSpace(request.CustomerName))
                {
                    sqlWhere += " AND ba.CustomerName LIKE CONCAT('%', @CustomerName, '%')";
                    dp.Add("CustomerName", request.CustomerName.Trim());
                }
                if (!string.IsNullOrWhiteSpace(request.TaxNo))
                {
                    sqlWhere += " AND ba.TaxId LIKE CONCAT('%', @TaxNo, '%')";
                    dp.Add("TaxNo", request.TaxNo.Trim());
                }
                if (request.StartDate.HasValue)
                {
                    sqlWhere += " AND ba.BillDate >= @StartDate";
                    dp.Add("StartDate", request.StartDate.Value.Date);
                }
                if (request.EndDate.HasValue)
                {
                    // 包含当天 23:59:59
                    sqlWhere += " AND ba.BillDate <= @EndDate";
                    dp.Add("EndDate", request.EndDate.Value.Date.AddDays(1).AddSeconds(-1));
                }

                var baseSql = @"
SELECT 
    ba.BillNumber      AS BillCode,
    ba.BillDate        AS BillDate,
    ba.CustomerName    AS CustomerName,
    ba.TaxId           AS TaxNo,
    ba.BankName        AS BankName,
    ba.ProductCode     AS BankAccount,
    ba.AddressPhone    AS AddressPhone,
    ba.TaxRate         AS TaxRate,
    SUM(bm.Quantity)   AS InvoiceQty,
    SUM(bm.Amount)     AS InvoiceAmount
FROM BillingAccounting ba
JOIN BillingMaterials bm ON bm.BillingAccountingId = ba.Id
" + sqlWhere + @"
GROUP BY ba.BillNumber, ba.BillDate, ba.CustomerName, ba.TaxId, ba.BankName, ba.ProductCode, ba.AddressPhone";

                // 统计总数
                var countSql = $"SELECT COUNT(1) FROM ( {baseSql} ) t";
                var totalCount = await conn.ExecuteScalarAsync<int>(new CommandDefinition(countSql, dp, cancellationToken: cancellationToken));

                // 排序 + 分页（MySQL 语法）
                dp.Add("Offset", offset);
                dp.Add("PageSize", pageSize);
                var pageSql = baseSql + " ORDER BY BillDate DESC, BillCode DESC LIMIT @Offset, @PageSize";

                var items = (await conn.QueryAsync<BillingNoticeDetailRowDto>(new CommandDefinition(pageSql, dp, cancellationToken: cancellationToken))).ToList();

                return new APIResult<GetBillingNoticeDetailListDto>
                {
                    Code =APIEnums.Success,
                    Msg = "查询成功",
                    Data = new GetBillingNoticeDetailListDto
                    {
                        TotalCount = totalCount,
                        Items = items
                    }
                };
            }
            catch (Exception ex)
            {
                return new APIResult<GetBillingNoticeDetailListDto>
                {
                    Code = APIEnums.Error,
                    Msg = $"查询失败: {ex.Message}",
                    Data = default
                };
            }
        }
    }
}
